package com.ruoyi.aitcommon.utils;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.aitcommon.vo.DBConVO;
import com.ruoyi.common.utils.StringUtils;
import cn.hutool.core.util.StrUtil;

/**
 * 将SQLUtils同DBUtils分开，确保执行完SQL后，资源都能释放掉
 * 
 * @author Administrator
 *
 */
public class SQLUtils extends SQLBase{

	/**
	 * 校验SQL合法性,防止SQL注入
	 * @param path
	 * @throws Exception
	 */
	public static void validQueryPath(String path) throws Exception {
		if(StrUtil.containsAnyIgnoreCase(path
				,"update ","delete ","select "
				," union "," from "," join "," exists "))
			throw new Exception("查询SQL不符合规范");
	}

//	通过正则表达式去掉备注
	public static String removeComment(String sqlstr) {
		/**
		 * 正则表达式正向匹配与反向匹配 
		 * http://www.xoxxoo.com/index/index/article/id/425
		 * 
		 * 正则表达式匹配注释，分为两部分： 
		 * 1、-- 注释,该注释已--开头，\r或\n或文档结尾 结束 ,特殊处理 排除 '-- 开头的字符
		 * (?<!\\')\\-\\-.*?(?=\\r|\\n|$)
		 * 2、/* 注释，该注释/*开头，中间包含(\\s|.)*?，以*\/为结束
		 * \\/\\*(\\s|.)*?\\*\\/
		 */
		String pattern = "(?<!\\')\\-\\-.*?(?=\\r|\\n|$)|\\/\\*(\\s|.)*?\\*\\/";

//		去掉注释
		Pattern compile = Pattern.compile(pattern);
		Matcher matcher = compile.matcher(sqlstr);
		String retstr = matcher.replaceAll("");

//		去掉末尾分号		
		retstr = retstr.trim();
		if (retstr.endsWith(";"))
			retstr = retstr.substring(0, retstr.length() - 1);
		return retstr;
	}

	public static List<Map<String, Object>> execProcQuery(String sql, JSONObject db) throws Exception{
		DBConVO db1 = JSONObject.toJavaObject(db, DBConVO.class);
		return execProcQuery(sql,db1);
	}
	public static List<Map<String, Object>> execProcQuery(String sql, DBConVO dbconf) throws Exception{
		if(dbconf==null)
			throw new Exception("数据库配置为空,请检查配置!");
		
		DBUtils db = new DBUtils(dbconf.url, dbconf.username, dbconf.password);
		return execProcQuery(sql,db);
	}

	
	public static int execProcUpdate(String sql, JSONObject db) throws Exception{
		DBConVO db1 = JSONObject.toJavaObject(db, DBConVO.class);
		return execProcUpdate(sql,db1);
	}
	public static int execProcUpdate(String sql, DBConVO dbconf) throws Exception{
		if(dbconf==null)
			throw new Exception("数据库配置为空,请检查配置!");
		
		DBUtils db = new DBUtils(dbconf.url, dbconf.username, dbconf.password);
		return execProcUpdate(sql,db);
	}
	
	
	/**
	 * 返回记录数SQL
	 * @param sql 查询sql
	 * @param path 查询条件
	 * @param db
	 * @return
	 * @throws Exception
	 */
	public static int getTotalCnt(String sql,String path,JSONObject db) throws Exception {
		int total=0;
		if(db==null)
			throw new Exception("传入DB为空");
		DBConVO db1 = JSONObject.toJavaObject(db, DBConVO.class);
		
//		Path处理
		if(StrUtil.isBlank(path)) {
			path="";
		}
		path=path.trim();
		if(StrUtil.isNotBlank(path)
				&& !StrUtil.startWithIgnoreCase(path, "and ")) {
			path=" and "+path;
		}
		
//		SQL处理
		String totalSQL = "select count(1) cnt from (%s) tmpsql "
				+ "where 1=1 %s ";
		totalSQL = String.format(totalSQL, sql,path);
		
//		SQL执行
		List<Map<String, Object>> ret = SQLUtils.execQuery(totalSQL, db1);
		if(ret.size()>0) {
			if(db1.isORACLE()) {
				total = Integer.parseInt(ret.get(0).get("CNT").toString());
			}else {
				total = Integer.parseInt(ret.get(0).get("cnt").toString());
			}
		}
		return total;
	}
	

	
//	MAP更新服务

	/**
	 * 依据关键字更新DB记录
	 * @param datas 数据集合
	 * @param db DB链接
	 * @param tablename 表名
	 * @param keys 关键字集合
	 * @param includes 包含更新,可以=null
	 * @param excludes 排除更新,可以=null
	 * @return
	 * @throws Exception
	 */
	public static int mapUpdate(List<Map<String, Object>> datas, JSONObject db, String tablename, List<String> keys,
			String includes, String excludes) throws Exception {
		DBConVO db1 = JSONObject.toJavaObject(db, DBConVO.class);
		List<String> sqls = SQLUtils.ListMap2Update(datas, tablename, keys, includes, excludes,db1);
		return SQLUtils.execUpdate(sqls, db1);
	}

	// SQL更新方法
	public static int execUpdate(String sql, DBConVO dbconf) throws Exception {
		if(dbconf==null)
			throw new Exception("数据库配置为空,请检查配置!");
		
		DBUtils db = new DBUtils(dbconf.url, dbconf.username, dbconf.password);
		return execUpdate(sql, db);
	}
	
	// SQL更新方法
	public static int execUpdate(String sql,List<Object> parms, DBConVO dbconf) throws Exception {
		if (StringUtils.isBlank(sql))
			throw new Exception("sql为空,请检查配置!");
		
		if(parms!=null&&parms.size()!=0)
			sql=String.format(sql, parms.toArray());
		return execUpdate(sql, dbconf);
	}
	
	// SQL更新方法
	public static int execUpdate(String sql,List<Object> parms) throws Exception {
		if (StringUtils.isBlank(sql))
			throw new Exception("sql为空,请检查配置!");
		
		if(parms!=null&&parms.size()!=0)
			sql=String.format(sql, parms.toArray());
		return execUpdate(sql);
	}

	// SQL更新方法
	public static int execUpdate(String sql) throws Exception {
		DBUtils db = new DBUtils();
		return execUpdate(sql, db);
	}

	// SQL批量更新方法
	public static int execUpdate(List<String> sqls) throws Exception {
		DBUtils db = new DBUtils();
		return execUpdate(sqls, db);
	}

	// SQL批量更新方法
	public static int execUpdate(List<String> sqls, DBConVO dbconf) throws Exception {
		if(dbconf==null)
			throw new Exception("数据库配置为空,请检查配置!");
		
		DBUtils db = new DBUtils(dbconf.url, dbconf.username, dbconf.password);
		return execUpdate(sqls, db);
	}


	/**
	 * 下述为SQL查询方法
	 */
	public static List<Map<String, Object>> execQuery(String sql, DBConVO dbconf) throws Exception {
		if(dbconf==null)
			throw new Exception("数据库配置为空,请检查配置!");
		DBUtils db = new DBUtils(dbconf.url, dbconf.username, dbconf.password);
		return execQuery(sql, db);
	}
	public static List<Map<String, Object>> execQuery(String sql, JSONObject db) throws Exception {
		DBConVO db1 = JSONObject.toJavaObject(db, DBConVO.class);
		return SQLUtils.execQuery(sql, db1);
	}
	public static List<Map<String, Object>> execQuery(String sql) throws Exception {
		DBUtils db = new DBUtils();
		return execQuery(sql, db);
	}
	public static List<Map<String, Object>> execQuery(String sql,List<Object> parms, DBUtils db) throws Exception {
		if(parms!=null&&parms.size()!=0)
			sql=String.format(sql, parms);
		return execQuery(sql,db);
	}

	/**
	 * 带参查询
	 * @param sql 查询sql
	 * @param params sql中的参数
	 * @param path 动态条件
	 * @param db 数据库连接
	 * @return
	 */
	public static List<Map<String, Object>> execQueryParams(String sql,List<Object> params,String path, JSONObject db) throws Exception {
		if(db==null)
			throw new Exception("传入DB为空");
		if(StrUtil.isBlank(sql))
			throw new Exception("传入SQL为空");
		if(params!=null&&params.size()!=0)
			sql=String.format(sql, params.toArray());
		return execQueryPath(sql,path, db);
	}
	/**
	 * 条件查询,使用Path动态拼接where
	 * @param sql 执行sql
	 * @param path 动态path
	 * @param db 数据库连接
	 * @return
	 */
	public static List<Map<String, Object>> execQueryPath(String sql,String path,JSONObject db) throws Exception{
		if(db==null)
			throw new Exception("传入DB为空");

//		Path判空处理
		if(StrUtil.isBlank(path)) {
			path="";
		}
		path=path.trim().toLowerCase();
		if(StrUtil.isNotBlank(path) && !StrUtil.startWith(path, "and")) {
			path=" and "+path;
		}

		String pathSQL="select * from (%s) tmpsql where 1=1 %s ";
		pathSQL=String.format(pathSQL, sql,path);
		List<Map<String,Object>> retlist = execQuery(pathSQL,db);
		return retlist;
	}

	/**
	 * 查询单行数据,返回某个字段
	 * @param sql 查询sql
	 * @param field 查询字段
	 * @return
	 */
	public static Object execQueryOne(String sql,String field) throws Exception{
		DBUtils db = new DBUtils();
		List<Map<String, Object>> retlist = execQuery(sql, db);
		if(retlist==null||retlist.size()==0)
			return null;
		if(!retlist.get(0).containsKey(field))
			return null;
		return retlist.get(0).get(field);
	}

	/**
	 * 分页查询,支持Path路径
	 * @param sql 查询sql
	 * @param path 查询条件
	 * @param order 排序条件
	 * @param db 数据库连接
	 * @param pageNum 页号
	 * @param pageSize 页大小
	 * @return
	 */
	public static List<Map<String, Object>> execQueryPage(String sql,String path,String order,JSONObject db
			,int pageNum,int pageSize) throws Exception {
		String pageSql="";
		if(db==null)
			throw new Exception("传入DB为空");
		DBConVO db1 = JSONObject.toJavaObject(db, DBConVO.class);

//		Path处理
		if(StrUtil.isBlank(path)) {
			path="";
		}
		if(StrUtil.isBlank(order)){
			order="";
		}
		path=path.trim();
		order=order.trim();
		if(StrUtil.isNotBlank(path) && !StrUtil.startWithIgnoreCase(path, "and ")) {
			path=" and "+path;
		}
		if(StrUtil.isNotBlank(order) && !StrUtil.startWithIgnoreCase(order, "order by ")){
			order=" order by "+order;
		}
//		SQL针对不同数据库进行分页处理
		if(db1.isMYSQL()) {
			pageSql="select * from (%s) tmpsql "
					+ "where 1=1 %s"
					+ "limit %s,%s ";
			pageSql=String.format(pageSql, sql,path,(pageNum-1)*pageSize,pageSize);
		}else if(db1.isORACLE()) {
			pageSql="select * \r\n"
					+ "from (select rownum num,tmpsql.*\r\n"
					+ "        from (%s) tmpsql\r\n"
					+ "        where 1=1 %s and rownum <= %s)\r\n"
					+ "where num > %s";
			pageSql=String.format(pageSql, sql,path,pageNum*pageSize,(pageNum-1)*pageSize);
		}else if(db1.isSQLSERVER()) {
//			默认按照ID正排序
			pageSql="select * from (\r\n"
					+ "select *,row_number() over(order by id) rownum from (\r\n%s\r\n) tmpsql\r\n"
					+ "where 1=1 %s\r\n"
					+ ") tmpsql1 \r\n" +
					"where tmpsql1.rownum>%s and tmpsql1.rownum<=%s %s";
			pageSql=String.format(pageSql, sql,path,(pageNum-1)*pageSize,pageNum*pageSize,order);
		}

//		SQL执行
		List<Map<String, Object>> ret = execQuery(pageSql, db);
		if(db1.isORACLE()) {
			for(Map<String,Object> item:ret) {
				item.remove("NUM");
			}
		}else if(db1.isSQLSERVER()) {
			for(Map<String,Object> item:ret) {
				//item.remove("id");
				item.remove("rownum");
			}
		}
		return ret;
	}

	/**
	 * 依据listmap拼接insert sql
	 * @param datas listmap
	 * @param table 插入表名
	 * @return sql集合
	 */
	public static List<String> ListMap2Insert(List<Map<String, Object>> datas, String table) throws Exception {
		return ListMap2Insert(datas, table, "", "",null);
	}

	/**
	 * 依据listmap拼接insert sql
	 * 
	 * @param datas    需要插入的数据
	 * @param table    插入表名
	 * @param includes 包含字段，逗号分隔，为空则插入所有字段
	 * @param excludes 排除字段，逗号分隔，为空则插入所有字段
	 * @return 插入SQL集合
	 * @throws Exception
	 */
	public static List<String> ListMap2Insert(List<Map<String, Object>> datas, String table, String includes,
			String excludes,DBConVO db) throws Exception {
		List<String> retlist = new ArrayList<String>();
		Map<String, Object> inmap = null;
		Map<String, Object> exmap = null;

		if (StringUtils.isNotBlank(includes)) {
			String[] split = StringUtils.split(includes, ',');
			inmap = MapUtils.newMap(split);
		}
		if (StringUtils.isNotBlank(excludes)) {
			String[] split = StringUtils.split(excludes, ',');
			exmap = MapUtils.newMap(split);
		}

		for (Map<String, Object> data : datas) {
			String sql = "insert into %s(%s) values(%s)";
			StringBuilder fields = new StringBuilder();
			StringBuilder values = new StringBuilder();
			for (Map.Entry<String, Object> item : data.entrySet()) {
				if (inmap != null && !inmap.containsKey(item.getKey()))
					continue;
				if (exmap != null && exmap.containsKey(item.getKey()))
					continue;
//				值为空的字段不会插入
				if (item.getKey() != null && item.getValue() != null) {
//					构造插入字段
					fields.append(item.getKey().toString()).append(",");
//					处理字段类型
					String valuestr = getValidValue(item.getKey().toString(), item.getValue(),db);
//					构造插入值
					values.append(valuestr).append(",");
				}
			}
			String fieldstr = fields.substring(0, fields.length() - 1);
			String valuestr = values.substring(0, values.length() - 1);
			sql = String.format(sql, table, fieldstr, valuestr);
			retlist.add(sql);
		}
		return retlist;
	}

	/**
	 * 获取 插入值/更新值 的有效值,将不同类型转换为字符串,并且去掉特殊字符
	 * 基于不同数据库,特殊处理
	 * @param key
	 * @param value
	 * @param db
	 * @return
	 */
	private static String getValidValue(String key, Object value,DBConVO db) throws Exception {
		String ret = "";
//		value校验
		if (value == null) {
			String msg = "字段[%s],值为空,不能插入或更新!";
			msg = String.format(msg, key);
			throw new Exception(msg);
		}
//		检查字段类型	
		if (value instanceof Map || value instanceof Iterable) {
			String msg = "字段[%s],值[%s],是Map或集合类型,不能插入或更新!";
			msg = String.format(msg, key, value.toString());
			throw new Exception(msg);
		}

//		依据值类型,处理sql语句
		if(value instanceof BigDecimal) {
//			数字
//			去掉多余的0,并且去掉科学计数法
			ret=((BigDecimal)value).stripTrailingZeros().toPlainString();
//			ret="'"+ret+"'";
		}else if(value instanceof java.util.Date) {
//			日期,翻译成字符串
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	        ret = sdf.format(value);
	        if(db!=null&&db.isORACLE()) {
//	        	Oracle目标字段和map数据字段相同
	        	ret="to_date('"+ret+"','yyyy-MM-dd HH24:mi:ss')";
//	        	Oracle目标字段必须是字符型,自动转义为字符型
//	        	ret="'"+ret+"'";
	        }else {
	        	ret="'"+ret+"'";
	        }
		}else {
//			其他类型
//			将value转换为string
			ret=value.toString();

//			特殊字符 单引号 处理
			if (StrUtil.contains(ret, "'")) {
				ret = StrUtil.replace(ret,"'", "''");
			}
//			特殊字符 反斜杠 处理
			if (StrUtil.contains(ret, "\\")) {
				ret = StrUtil.replace(ret,"\\", "\\\\");
			}

//			字符串特殊字符处理,使用unicode编码
			if(db!=null&&db.isSQLSERVER()){
				//SQLSERVER 添加N前缀,代表是一个Unicode字符常量
				ret="N'"+ret+"'";
			}else{
//				MySQL 使用 utf8 或 utf8mb4 字符集来支持 Unicode。
//				通常情况下，你不需要像在 SQL Server 中那样使用特定的前缀（如 N）来表示 Unicode 字符串。
				ret="'"+ret+"'";
			}
		}
		return ret;
	}

	public static List<String> ListMap2Update(List<Map<String, Object>> datas, String table, List<String> keys,
			String includes, String excludes,DBConVO db) throws Exception {
		List<String> retlist = new ArrayList<String>();
		Map<String, Object> inmap = null;
		Map<String, Object> exmap = null;
		Map<String,Object> keymap=null;
		if(keys==null)
			throw new Exception("key不能为空");
		keymap=MapUtils.newMap(keys);
		if (StrUtil.isNotBlank(includes)) {
			String[] split = includes.split(",");
			inmap = MapUtils.newMap(split);
		}
		if (StrUtil.isNotBlank(excludes)) {
			String[] split = excludes.split(",");
			exmap = MapUtils.newMap(split);
		}
		

		for (Map<String, Object> data : datas) {
//			传入的每行数据
			String sql = "update %s set %s where 1=1 %s";
			StringBuilder setvalues = new StringBuilder();
			StringBuilder pathvalues=new StringBuilder();
			
			for (Map.Entry<String, Object> item : data.entrySet()) {
//				每一行的每列数据
//				如果包含列中不包含该字段且key中不包含该字段,则跳过
				if (inmap != null && !inmap.containsKey(item.getKey()) && !keymap.containsKey(item.getKey()))
					continue;
//				如果排除列中包含该字段,则跳过
				if (exmap != null && exmap.containsKey(item.getKey()))
					continue;
//				值为空的字段,不会更新
				if (item.getKey() != null && item.getValue() != null) {
//					value兼容性处理
					String valuestr = getValidValue(item.getKey().toString(), item.getValue(),db);
//					key字段不用更新
					if(keymap.containsKey(item.getKey())) {
//						构造update where语句
						pathvalues.append(" and "+item.getKey()+" = "+valuestr);
					}else {
//						构造update set语句
						setvalues.append(item.getKey() + "=" + valuestr + ",");
					}
				}
			}
			if(setvalues.length()==0)
				throw new Exception("update字段匹配为空");
			if(pathvalues.length()==0)
				throw new Exception("path字段匹配为空,path字段:"+keymap);
			String setvaluestr = setvalues.substring(0, setvalues.length() - 1);
			sql = String.format(sql, table, setvaluestr, pathvalues);
			retlist.add(sql);
		}
		return retlist;
	}

	public static List<String> ListMap2Delete(List<Map<String, Object>> datas, String table, String key) {
		List<String> retlist = new ArrayList<String>();

		for (Map<String, Object> data : datas) {
			String sql = "delete from %s where %s";
			String pathstr = key + "='" + data.get(key) + "'";
			sql = String.format(sql, table, pathstr);
			retlist.add(sql);
		}
		return retlist;
	}
	
	/**
	 * 查找目标库是否存在该记录,支持Key集合 
	 * @param data 单条数据
	 * @param table 表名称
	 * @param keys 字段集合
	 * @return sql语句
	 * @throws Exception 
	 */
	public static String ListMap2Has(Map<String,Object> data
			,String table,List<String> keys) throws Exception{
		List<String> pathList=new ArrayList<String>();
		
		if(data==null)
			return "";
		
		for(String key:keys) {
			if(!data.containsKey(key)) {
				ExceptionUtils.throwException(null,"数据中不存在key字段[{}]", key);
			}
			String value = data.get(key).toString();
			String path=key+" = '"+value+"'";
			pathList.add(path);
		}
		String pathstr=StrUtil.join(" and ", pathList);

		String sql="select * from %s where 1=1 and %s";
		sql=String.format(sql,table,pathstr);
		return sql;
	}
}
